﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.ComponentModel;
using System.Configuration;

namespace Final.App_Code
{
    [DataObject]
    public class Studentsubject_allocation
    {
        
        private string status;
        private string priority;
        private string priority2;
        private string priority3;
        private string studentid;
        private string subjectcode;
        private string subjectcode2;
        private string subjectcode3;
        private DateTime submissiontime;

        public string Subjectcode2
        {
            get { return subjectcode2; }
            set { subjectcode2 = value; }
        }

        public string Subjectcode3
        {
            get { return subjectcode3; }
            set { subjectcode3 = value; }
        }

        public string Status
        {
            get { return status; }
            set { status = value; }
        }

        public string Priority
        {
            get { return priority;}
            set {  priority = value;}         
        }
        public string Priority2
        {
            get { return priority2;}
            set { priority2 = value;}
        }
        public string Priority3
        {
            get { return priority3; }          
            set { priority3 = value;}
        }

        public string Studentid
        {
            get { return studentid;}
            set { studentid = value;}
        }
        public string Subjectcode
        {
            get { return subjectcode;}
            set { subjectcode = value;}
        }
        public DateTime Submissiontime
        {
            get { return submissiontime;}
            set {  submissiontime = value;}
        }

        public static void insertstudentsubjectAllocation(Studentsubject_allocation sa)
        {

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SubjectManagementDBString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "Insert into StudentSubject_Allocation (id,Subject_Code,Submission_Time,Status,Priority)" +
                " VALUES (@id,@subjectcode,@submissiontime,@status,@priority)";


                String status = "Pending";
                comm.Parameters.AddWithValue("@id", sa.studentid);
                comm.Parameters.AddWithValue("@subjectcode", sa.subjectcode);
                comm.Parameters.AddWithValue("@submissiontime", sa.submissiontime);
                comm.Parameters.AddWithValue("@status", status);
                comm.Parameters.AddWithValue("@priority", sa.priority);

                int a = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

        }


        public static void insertstudentsubjectAllocationrow2(Studentsubject_allocation sa)
        {

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SubjectManagementDBString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "Insert into StudentSubject_Allocation (id,Subject_Code,Submission_Time,Status,Priority)" +
                " VALUES (@id,@subjectcode,@submissiontime,@status,@priority)";


                String status = "Pending";

                comm.Parameters.AddWithValue("@id", sa.studentid);
                comm.Parameters.AddWithValue("@subjectcode", sa.subjectcode2);
                comm.Parameters.AddWithValue("@submissiontime", sa.submissiontime);
                comm.Parameters.AddWithValue("@status", status);
                comm.Parameters.AddWithValue("@priority", sa.priority2);

                int a = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

        }

        public static void insertstudentsubjectAllocationrow3(Studentsubject_allocation sa)
        {

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SubjectManagementDBString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "Insert into StudentSubject_Allocation (id,Subject_Code,Submission_Time,Status,Priority)" +
                " VALUES (@id,@subjectcode,@submissiontime,@status,@priority)";

                String status = "Pending";

                comm.Parameters.AddWithValue("@id", sa.studentid);
                comm.Parameters.AddWithValue("@subjectcode", sa.subjectcode3);
                comm.Parameters.AddWithValue("@submissiontime", sa.submissiontime);
                comm.Parameters.AddWithValue("@status", status);
                comm.Parameters.AddWithValue("@priority", sa.priority3);

                int a = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

        }

        [DataObjectMethod(DataObjectMethodType.Select)]
        public static List<Student> getallstudentinfo()
        {
            SqlConnection conn = null;
            List<Student> s = new List<Student>();
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SubjectManagementDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select id,Student_Name from Account";

                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    Student a = new Student();
                    a.Adminno = (string)dr["id"];
                    a.Name = (string)dr["Student_Name"];

                    s.Add(a);

                }
                dr.Close();

            }
            catch (SqlException ex)
            {
                throw ex;

            }
            finally
            {
                conn.Close();


            } return s;

        }

        [DataObjectMethod(DataObjectMethodType.Select)]
        public static List<Studentsubject_allocation> getPriority(string Adminno)
        {
            SqlConnection conn = null;
            List<Studentsubject_allocation> s = new List<Studentsubject_allocation>();

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SubjectManagementDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select Priority from StudentSubject_Allocation where Id = @Id";
                comm.Parameters.AddWithValue("@Id", Adminno);
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    Studentsubject_allocation ssa = new Studentsubject_allocation();
                    ssa.priority = (string)dr["Priority"];
                    //need priority 2 and 3
                    s.Add(ssa);

                }
                dr.Close();

            }
            catch (SqlException ex)
            {
                throw ex;

            }
            finally
            {
                conn.Close();


            } return s;

        }

        [DataObjectMethod(DataObjectMethodType.Select)]
        public static DateTime getsubmittedtime(string Adminno)
        {

            SqlConnection conn = null;
            DateTime t = DateTime.Now;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SubjectManagementDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select Submission_Time from StudentSubject_Allocation where id = @id";
                comm.Parameters.AddWithValue("@id", Adminno);

                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    t = (DateTime)dr["Submission_Time"];
                }
                dr.Close();

            }
            catch (SqlException e)
            {
                throw e;

            }
            finally
            {
                conn.Close();


            }
            return t;
        }


        public static void updateStudentInfo(Student e)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SubjectManagementDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Account SET Module = @Module WHERE Id = @Id";
                comm.Parameters.AddWithValue("@Module", e.Module);
                comm.Parameters.AddWithValue("@Id", e.Adminno);
                int row = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }


        public static void updateRejectedstudentstatus(Student e)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SubjectManagementDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Student SET Module = @Module WHERE id = @id";
                comm.Parameters.AddWithValue("@Module", e.Module);
                comm.Parameters.AddWithValue("@id", e.Adminno);
                int row = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

    }

}
